#!/usr/bin/python
#coding=utf-8
#

import pymysql
from movieproject.items import MovieItem,MovieDetailItem

class SqlBase(object):
    def __init__(self):

        self.dbName= self.database_Name()
        self.tbCurTable= None
        self.conn = None
        self.cur = None

        self._init()
        self._close()

        pass

    # overwrite function 
    def database_Name(self):
        return "dbTest"
        pass

    def config(self):
        return ('localhost','root','Xq123456')
        pass

    def table_Info(self):
        table = {
        }
        return table
        pass

    # private function sql string
    def _createdbsql(self,databasename):
        sql = "CREATE database if not exists %s character set utf8;" % databasename
        #print sql
        return sql
        pass

    def _createtablesql(self,tablename,item):
        easysql = 'CREATE TABLE if not exists ' + tablename + ' ( id int(10) unsigned NOT NULL AUTO_INCREMENT,%s PRIMARY KEY (id));'
        
        table = "%s varchar(80) NOT NULL DEFAULT '',"* len(item.fields.keys())
        table = table % tuple(item.fields.keys())
        easysql = easysql % table
        #print easysql 
        return easysql
        pass

    def _dropdatabasesql(self,db):
        sql = 'DROP DATABASE if exists ' + db
        
        return sql
        pass

    def _createinsertSql(self,tablename,data):

        sql = "INSERT INTO " + tablename + " (" + ",".join(data.keys()) + ") VALUES ('" + "','".join(data.values()).encode('UTF-8') + "')"
        #print sql
        return sql
        pass

    # private function
    def _init(self):
        try:
            # connect
            self.conn=pymysql.connect(host=self.config()[0],user=self.config()[1],passwd=self.config()[2],port=3306,charset="utf8")
            self.cur=self.conn.cursor()

            # delete database
            self.cur.execute(self._dropdatabasesql(self.dbName))  
            # create database
            self.cur.execute(self._createdbsql(self.dbName))
            self.conn.select_db(self.dbName)

            # create tables
            for item in self.table_Info().items():
                self.cur.execute(self._createtablesql(item[0],item[1]))
                pass
            pass

        except pymysql.Error,e:
            print u"Mysql Error %d: %s" % (e.args[0], e.args[1])
        finally:
            pass
        pass

    def _open(self):
        try:
            self.conn=pymysql.connect(host=self.config()[0],user=self.config()[1],passwd=self.config()[2],port=3306,charset="utf8")
            self.cur=self.conn.cursor()   
            self.conn.select_db(self.dbName)    

        except pymysql.Error,e:
            print u"Mysql Error %d: %s" % (e.args[0], e.args[1])
            self._close()
        finally:
            pass
        pass

    def _close(self):
        self.cur.close()
        self.conn.close()
        self.conn = None
        self.cur = None
        pass

    # insert
    def insert(self,tbName,item):
        try:
            self.tbCurTable = tbName
            self._open()
            
            sql = self._createinsertSql(tbName,item)
            self.cur.execute(sql)
            self.conn.commit()
            
            self._close()

        except pymysql.Error,e:
            print u"Mysql Error %d: %s" % (e.args[0], e.args[1])
        finally:
            pass

        pass

    def insertMulit(self,tbName,items):
        pass

    def update(self,sql):

        pass

    def delete(self,sql):

        pass
 
class Mp4BaSql(SqlBase):

    def table_Info(self):
        table = {
        'tbMovieItem':MovieItem(),
        'tbMovieDetailItem':MovieDetailItem()
        }
        return table
        pass